import { query, testConnection } from "./db";
export * from './services/index'

/**
 * 数据库初始化脚本
 * 创建必要的表结构
 */
export async function initializeDatabase() {
  try {
    console.log("开始初始化数据库...");

    // 测试连接
    const isConnected = await testConnection();
    if (!isConnected) {
      throw new Error("数据库连接失败");
    }

    console.log("数据库连接成功");

    // 创建视频表
    await query(`
      CREATE TABLE IF NOT EXISTS videos (
        id VARCHAR(50) PRIMARY KEY,
        title VARCHAR(255) NOT NULL,
        cover_url VARCHAR(500),
        duration_seconds INT DEFAULT 0,
        category INT NOT NULL,
        description TEXT,
        source_url VARCHAR(2048),
        published_at TIMESTAMP NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        average_rating DECIMAL(3, 2) DEFAULT 0.00 COMMENT '平均评分',
        rating_count INT DEFAULT 0 COMMENT '评分人数'
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);
    // 插入默认视频数据


    // 创建分类表
    await query(`
      CREATE TABLE IF NOT EXISTS categories (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL UNIQUE,
        cover_url VARCHAR(500),
        video_count INT DEFAULT 0,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 插入默认分类数据
    const categories = [
      { name: "电影", description: "各种类型的电影作品", id: 2025102901, count: 0, cover: 'https://inglescci.com/wp-content/uploads/2024/06/UpdatedEmily-Henderson-Current-Animated-Movies-1670x1541-1-1024x945.jpg' },
      { name: "电视剧", description: "连续剧和系列剧", id: 2025102902, count: 0, cover: 'https://static1.colliderimages.com/wordpress/wp-content/uploads/2021/05/Best-Dramas-Netflix.jpg' },
      { name: "综艺", description: "综艺节目和娱乐节目", id: 2025102903, count: 0, cover: 'https://ts2.tc.mm.bing.net/th/id/OIP-C.ae_g1yEASmCDu4KaRD0TqAHaHH?rs=1&pid=ImgDetMain&o=7&rm=3' },
      { name: "动漫", description: "动画和漫画作品", id: 2025102904, count: 0, cover: 'https://static.wixstatic.com/media/918626_30add40d5f294ae5b8426085bcec6f6f~mv2.png/v1/fit/w_1000%2Ch_1000%2Cal_c/file.png' },
      { name: "纪录片", description: "纪录片和教育内容", id: 2025102905, count: 0, cover: 'https://pbblogassets.s3.amazonaws.com/uploads/2015/12/ice-documentary.jpg' },
      { name: "其他", description: "其他类型的视频内容", id: 2025102906, count: 0, cover: 'https://thepedalzone.com/wp-content/uploads/2022/08/tpz-blog-banners-mob-other.jpg' },
    ];

    for (const { id, name,  description, cover, count } of categories) {
      await query(
        `
        INSERT IGNORE INTO categories (id, name, description, cover_url, video_count) 
        VALUES (?, ?, ?, ?, ?)
      `,
        [id, name,  description, cover, count]
      );
    }
    await query(
      `
      INSERT INTO videos (id, title, cover_url, duration_seconds, category, description, source_url, published_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `,
      [
        "v1",
        "令人心动的offer 第七季",
        "https://img3.doubanio.com/view/photo/s_ratio_poster/public/p2924817257.jpg",
        5420,
        2025102903,
        "《令人心动的offer 第七季》定位为一档讲述职场新人成长蜕变的职场观察类真人秀。节目将记录一群有志于投身传媒的优秀学子前往东方卫视，开启为期30天的热血实习，并以嘉宾观察团的观察推理引发话题讨论，描绘大学生群体离开校园、初入职场的热血成长图鉴。",
        "https://test-streams.mux.dev/x36xhzz/x36xhzz.m3u8",
        "2025-10-27 00:00:00",
      ]
    );
    await query(
      `
      INSERT INTO videos (id, title, cover_url, duration_seconds, category, description, source_url, published_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `,
      [
        "v2",
        "极限挑战 第四季",
        "https://img1.doubanio.com/view/photo/s_ratio_poster/public/p2520462368.jpg",
        1800,
        2025102903,
        "极限挑战 第四季",
        "https://bitdash-a.akamaihd.net/content/sintel/hls/playlist.m3u8",
        "2024-07-04 00:00:00",
      ]
    );
    await query(
      `
      INSERT INTO videos (id, title, cover_url, duration_seconds, category, description, source_url, published_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
    `,
      [
        "v3",
        "同甘共苦",
        "https://img3.doubanio.com/view/photo/s_ratio_poster/public/p2922906363.jpg",
        6102,
        2025102901,
        "多年情侣蒂姆（戴夫·弗兰科 Dave Franco 饰）和米莉（爱丽森·布里 Alison Brie 饰）来到感情脆弱的关口，开始思考爱是……为了挽回渐淡的感情，决定抛开朋友和工作搬到陌生乡村展开全新二人世界。然而新生活未如理想，更卷入了当地一场超自然力量的恐怖漩涡。身体及意识渐渐变得不受控，当他们的心愈远，肉体却愈贴愈近，肌肤甚至开始无缝结合……电锯是否还给大家自由的唯一解救方法？但染血的爱又那可修补",
        "https://console.lookbook.top/api/yz_siyou/82880b979caaed7d85d429c8abd025da.m3u8?ZmIwZXdnbEIzK1FrSDR2aUxhdU5iQkdDRzVtZ3lKNUMyUGR5UmM2NldSajNRUlE2OXhnVUJQcnh4RHNGNzR2SXVsT0tUYWZURTQxUFFUTTZra2YxZmpybkhWT3ZLdCtybXhjQVZ0dk5lMllnekt3Mm5sUjBWelNzOGQ4bWN1U0h3bUNBaHMzSFJOMmp3WGtBRWNlQjNtMHFDZ0l2em9BVnVoN0hlMVRQWVdWcWIwYVBwMTRiYkNHZU1CODRqUFhOMTJZdmp2aTRLN1lIc3dTbFYxQ1BqTitMTVExSkZyazFaVE5GNlRLbTRnaEtFUHJKYTdXYzE4QlVKbjR3OGdVWVdnS3ZzMjZCaWdKQXNOQkJYL1VFUUhxTzI5bFpGM3JQcmtMcytIeE1zU2tJWXVOdjgzU1BDMlVVRG1JcG5iMUEyeUhXbmRHUmtOUzhQUUtuLzErM05FYU4wN0FRY29scVVsWDA2NU9teStMVk5Fb3prVys1cXNKQzBzcHNWNVpwb0l4V1ZYUlozZFFuN3Rwb1hB",
        "2025-09-20 00:00:00",
      ]
    );

    // 创建用户表（如果需要用户功能）
    await query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) NOT NULL UNIQUE,
        email VARCHAR(100) NOT NULL UNIQUE,
        password_hash VARCHAR(255) NOT NULL,
        avatar_url VARCHAR(500),
        role ENUM('admin', 'user') DEFAULT 'user',
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        INDEX idx_username (username),
        INDEX idx_email (email),
        INDEX idx_role (role)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建播放历史表
    await query(`
      CREATE TABLE IF NOT EXISTS play_history (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT,
        video_id VARCHAR(50) NOT NULL,
        play_duration INT DEFAULT 0,
        total_duration INT DEFAULT 0,
        played_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
        INDEX idx_user_id (user_id),
        INDEX idx_video_id (video_id),
        INDEX idx_played_at (played_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建评分表
    await query(`
      CREATE TABLE IF NOT EXISTS ratings (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        video_id VARCHAR(50) NOT NULL,
        rating TINYINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
        UNIQUE KEY uk_user_video (user_id, video_id),
        INDEX idx_user_id (user_id),
        INDEX idx_video_id (video_id)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    // 创建收藏表
    await query(`
      CREATE TABLE IF NOT EXISTS favorites (
        id INT AUTO_INCREMENT PRIMARY KEY,
        user_id INT NOT NULL,
        video_id VARCHAR(50) NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (video_id) REFERENCES videos(id) ON DELETE CASCADE,
        UNIQUE KEY uk_user_video (user_id, video_id),
        INDEX idx_user_id (user_id),
        INDEX idx_video_id (video_id),
        INDEX idx_created_at (created_at)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
    `);

    console.log("数据库初始化完成！");
  } catch (error) {
    console.error("数据库初始化失败:", error);
    throw error;
  }
}